package cn.zy.management;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBUtil {

	public DBUtil(String server, String port, String database, String username, String password,
			String tablename, String column1, String column2) {
		this.setServer(server);
		this.setPort(port);
		this.setDatabase(database);
		this.setUsername(username);
		this.setPassword(password);
		this.setTablename(tablename);
		this.setColumn1(column1);
		this.setColumn2(column2);
	}
	/*
	public List<String> getNameFromIPAddress(String ipAddress) throws SQLException {
		ArrayList<String> nameList = new ArrayList<String>();
		String name = null;
		Statement stmt = connection.createStatement();
		ResultSet result = stmt.executeQuery("select " + column1 + " from " + tablename + " where ip = '" + ipAddress +"'");
		
		while (result.next()) {
			name = result.getString("name");
			nameList.add(name);
		}
		
		return nameList;
	}
	
	public List<String> getIPAddressFromName(String name) throws SQLException {
		ArrayList<String> addressList = new ArrayList<String>();
		String address = null;
		Statement stmt = connection.createStatement();
		ResultSet result = stmt.executeQuery("select ip from " + tablename + " where name = '" + name + "'");
		
		while (result.next()) {
			address = result.getString("ip");
			addressList.add(address);
		}
		
		return addressList;
	}
	
	public List<String> getAllNames() throws SQLException {
		ArrayList<String> allNameList = new ArrayList<String>();
		String name = null;
		Statement stmt = connection.createStatement();
		ResultSet result = stmt.executeQuery("select distinct name from " + tablename);
		
		while (result.next()) {
			name = result.getString("name");
			allNameList.add(name);
		}
		
		return allNameList;
	}
	
	public List<String> getAllIPAddresses() throws SQLException {
		ArrayList<String> allIPList = new ArrayList<String>();
		String ip = null;
		Statement stmt = connection.createStatement();
		ResultSet result = stmt.executeQuery("select distinct ip from " + tablename);
		
		while (result.next()) {
			ip = result.getString("ip");
			allIPList.add(ip);
		}
		
		return allIPList;
	}*/
	
	public boolean testConnection(){
		
		url = "jdbc:microsoft:sqlserver://" + server + ":" + port + ";DatabaseName=";
		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		Statement stmt = null;;
		ResultSet result = null;
		try {
			connection = DriverManager.getConnection(url + this.database, this.username, this.password);
			stmt = connection.createStatement();
			result = stmt.executeQuery("select 1 from " + tablename);
			
			if (result.next())
				return true;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public StringPair getAllNameAndIP() throws SQLException {
		StringPair pair = new StringPair();
		String name = null;
		String ip = null;
		Statement stmt = connection.createStatement();
		ResultSet result = stmt.executeQuery("select " + column1 + ", " + column2 + " from " + tablename);
		
		while (result.next()) {
			name = result.getString(column1);
			ip = result.getString(column2);
			
			pair.put(name, ip);
		}
		
		return pair;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getTablename() {
		return tablename;
	}

	public void setTablename(String tablename) {
		this.tablename = tablename;
	}

	public String getDatabase() {
		return database;
	}

	public void setDatabase(String database) {
		this.database = database;
	}

	public String getColumn1() {
		return column1;
	}

	public void setColumn1(String column1) {
		this.column1 = column1;
	}

	public String getColumn2() {
		return column2;
	}

	public void setColumn2(String column2) {
		this.column2 = column2;
	}
	
	public String getServer() {
		return server;
	}

	public void setServer(String server) {
		this.server = server;
	}

	public String getPort() {
		return port;
	}

	public void setPort(String port) {
		this.port = port;
	}

	private String url;
	private String server;
	private String port;
	private String database;
	private String username;
	private String password;
	private String tablename;
	private String column1;
	private String column2;
	private Connection connection;
}
